package com.dao;

import com.bean.Phone;
import com.utils.DBHelper;

import java.sql.*;
import java.util.ArrayList;
import java.sql.Date;
import java.util.List;

public class PhoneDao {
    /**
     * 添加方法
     * @param name
     * @param price
     * @param model
     * @param color
     * @param origin
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void insert(String name, float price, String model, String color, String origin) throws SQLException, ClassNotFoundException {
        String sql = "insert into phone (phone_name, phone_price, phone_model, phone_color, phone_origin) values (?, ?, ?, ?, ?)";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)){
            statement.setString(1, name);
            statement.setFloat(2, price);
            statement.setString(3, model);
            statement.setString(4, color);
            statement.setString(5, origin);
            statement.executeUpdate();
        }
    }

    /**
     * 删除方法
     * @param id
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void delete(int id) throws SQLException, ClassNotFoundException {
        String sql = "delete from phone where phone_id = ?";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql)){
            statement.setInt(1, id);

            int rows = statement.executeUpdate();
            System.out.println("删除的行数：" + rows);
        }
    }

    /**
     * 查询所有信息方法
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List<Phone> list() throws SQLException, ClassNotFoundException {
        List<Phone> phones = new ArrayList<>();
        String sql = "select phone_id, phone_name, phone_price, phone_model, phone_color, phone_origin, phone_time from phone";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()){
            while (resultSet.next()) {
                // 简洁的写法
                phones.add(
                        new Phone(
                                resultSet.getInt(1),
                                resultSet.getString(2),
                                resultSet.getFloat(3),
                                resultSet.getString(4),
                                resultSet.getString(5),
                                resultSet.getString(6),
                                resultSet.getDate(7)
                        )
                );
            }
        }
        return phones;
    }

    /**
     * 模糊查询方法
     * condition 关键字是按条件来搜索的
     * @param condition
     * @return
     */
    public List<Phone> list(String condition) throws SQLException, ClassNotFoundException {
        List<Phone> phones = new ArrayList<>();
        // sql 语句是按照手机名称和手机产地来进行查询的
        String sql = "select phone_id, phone_name, phone_price, phone_model, phone_color, phone_origin, phone_time from phone where phone_name like ?";

        try (Connection connection = DBHelper.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql)){
            statement.setString(1, "%" + condition + "%");
            try (ResultSet resultSet = statement.executeQuery()){
                while (resultSet.next()) {
                    // 简洁的写法
                    phones.add(
                            new Phone(
                                    resultSet.getInt(1),
                                    resultSet.getString(2),
                                    resultSet.getFloat(3),
                                    resultSet.getString(4),
                                    resultSet.getString(5),
                                    resultSet.getString(6),
                                    resultSet.getDate(7)
                            )
                    );
                }
                return phones;
            }
        }
    }

    /**
     * 模糊查询（按价格查询）
     * @param condition1
     * @param condition2
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List<Phone> vague(float condition1, float condition2) throws SQLException, ClassNotFoundException {
        List<Phone> phones = new ArrayList<>();
        String sql = "select phone_id, phone_name, phone_price, phone_model, phone_color, phone_origin, phone_time from phone where phone_price between ? and ?";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql)){
            statement.setFloat(1, condition1);
            statement.setFloat(2, condition2);
            try (ResultSet resultSet = statement.executeQuery()){
                while (resultSet.next()) {
                    // 简洁的写法
                    phones.add(
                            new Phone(
                                    resultSet.getInt(1),
                                    resultSet.getString(2),
                                    resultSet.getFloat(3),
                                    resultSet.getString(4),
                                    resultSet.getString(5),
                                    resultSet.getString(6),
                                    resultSet.getDate(7)
                            )
                    );
                }
                return phones;
            }
        }
    }

    /**
     * 查询单条记录方法
     * 修改之前要先查询一条记录，然后再进行修改
     * @param id
     * @return
     */
    public Phone get(int id) throws SQLException, ClassNotFoundException {
        String sql = "select phone_name, phone_price, phone_model, phone_color, phone_origin, phone_time from phone where phone_id = ?";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql)){
            statement.setInt(1, id);
            try (ResultSet resultSet = statement.executeQuery()){
                if (resultSet.next()) {
                    String name = resultSet.getString(1);
                    float price = resultSet.getFloat(2);
                    String model = resultSet.getString(3);
                    String color = resultSet.getString(4);
                    String origin = resultSet.getString(5);
                    Date time = resultSet.getDate(6);

                    Phone phone = new Phone();
                    phone.setId(id);
                    phone.setName(name);
                    phone.setPrice(price);
                    phone.setModel(model);
                    phone.setColor(color);
                    phone.setOrigin(origin);
                    phone.setTime(time);
                    return phone;
                }
                return null;
            }
        }
    }

    /**
     * 修改方法，先查询一条记录再修改
     * @param id
     * @param name
     * @param price
     * @param model
     * @param color
     * @param origin
     * @param time
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void update(int id, String name, float price, String model, String color, String origin, Date time) throws SQLException, ClassNotFoundException {
        String sql = "update phone set phone_name = ?, phone_price = ?, phone_model = ?, phone_color = ?, phone_origin = ?, phone_time = ? where phone_id = ?";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql)){
            statement.setString(1, name);
            statement.setFloat(2, price);
            statement.setString(3, model);
            statement.setString(4, color);
            statement.setString(5, origin);
            statement.setDate(6, time);
            statement.setInt(7, id);
            statement.executeUpdate();
        }
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        System.out.println(new PhoneDao().get(1));
    }
}
